package rursu.dal.managers;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper {

	private static final String LOGGER_TAG = DataBaseHelper.class.getCanonicalName();
	
	public DataBaseHelper( Context context, String databaseName, int databaseVersion ) {
		super(context, databaseName, null, databaseVersion);
	}
	
	@Override
	public void onCreate( SQLiteDatabase db ) {
		createTables(db);		
	}

	@Override
	public void onUpgrade( SQLiteDatabase db, int oldVersion, int newVersion ) {
		Log.w(LOGGER_TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
		
		deleteTables( db );        
        onCreate( db );
	}

	public void createTables( SQLiteDatabase db ) {
		createMoviesTable( db );
		createMoviesListTable( db );
		createMoviesListPairTable( db );
		createMovieDetailsTable( db );
		createMovieImageTable( db );
		createSearchTable( db );
	}	
	
	private void deleteTables( SQLiteDatabase db ) {
		db.execSQL("DROP TABLE IF EXISTS " + MovieDALManager.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MoviesListDALManager.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MoviesListPairDALManager.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MovieDetailsDALManager.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MovieImageDALManager.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MovieSearchDALManager.TABLE_SEARCH_NAME);
	}
	
	private void createMoviesTable( SQLiteDatabase db ) {
		db.execSQL("create table " + MovieDALManager.TABLE_NAME + " ("
				+ MovieDALManager.ID 					+ 	" INTEGER PRIMARY KEY , "
		        + MovieDALManager.SCORE      			+	" INTEGER, "
		        + MovieDALManager.CERTIFICATION 		+ 	" TEXT, "
				+ MovieDALManager.ADULT      			+	" TEXT, "
				+ MovieDALManager.ORIGINAL_NAME 		+ 	" TEXT, "
		        + MovieDALManager.NAME      			+	" TEXT, "
				+ MovieDALManager.ALTERNATIVE_NAME 		+ 	" TEXT, "
				+ MovieDALManager.LAST_MODIFIED 		+ 	" TEXT, "
		        + MovieDALManager.TYPE      			+	" TEXT, "
		        + MovieDALManager.RELEASED      		+	" TEXT, "
		        + MovieDALManager.OVERVIEW      		+	" TEXT, "
				+ MovieDALManager.RATING 				+ 	" REAL, "
				+ MovieDALManager.VOTES 				+ 	" INTEGER, "
				+ "FOREIGN KEY(" + MovieDALManager.ID + ") REFERENCES " + MovieDetailsDALManager.TABLE_NAME + "(" + MovieDetailsDALManager.ID + ")"
		        + ");");
	}
	
	private void createSearchTable(SQLiteDatabase db) {
		db.execSQL("create table " + MovieSearchDALManager.TABLE_SEARCH_NAME + " ("
				+ MovieSearchDALManager.ID 			+ 	" INTEGER PRIMARY KEY AUTOINCREMENT , "
		        + MovieSearchDALManager.QUERY   	+	" INTEGER "			
		        + ");");
	}
	
	private void createMoviesListTable( SQLiteDatabase db ) {
		db.execSQL("create table " + MoviesListDALManager.TABLE_NAME + " ("
				+ MoviesListDALManager.ID 					+ 	" INTEGER PRIMARY KEY AUTOINCREMENT, "
		        + MoviesListDALManager.ORDER_BY      			+	" TEXT,"
		        + MoviesListDALManager.DATE      				+	" LONG"
		        + ");");
	}
	
	private void createMoviesListPairTable( SQLiteDatabase db ) {
		db.execSQL("create table " + MoviesListPairDALManager.TABLE_NAME + " ("
				+ MoviesListPairDALManager.ID		 			+ 	" INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ MoviesListPairDALManager.LIST_ID 			+ 	" INTEGER, "
		        + MoviesListPairDALManager.MOVIE_ID  			+	" INTEGER, "
		        + "FOREIGN KEY(" + MoviesListPairDALManager.LIST_ID + ") REFERENCES " + MoviesListDALManager.TABLE_NAME + "(" + MovieDALManager.ID + "),"
		        + "FOREIGN KEY(" + MoviesListPairDALManager.MOVIE_ID + ") REFERENCES " + MovieDALManager.TABLE_NAME + "(" + MovieDALManager.ID + ")"
		        + ");");

	}
	
	private void createMovieDetailsTable( SQLiteDatabase db ) {
		db.execSQL("create table " + MovieDetailsDALManager.TABLE_NAME + " ("
				+ MovieDetailsDALManager.ID		 			+ 	" INTEGER PRIMARY KEY, "
		        + MovieDetailsDALManager.TRAILER  			+	" TEXT,"
		        + MovieDetailsDALManager.LAST_MODIFIED		+	" TEXT,"
		        + MovieDetailsDALManager.CAST  				+	" TEXT, "
		        + "FOREIGN KEY(" + MovieDetailsDALManager.ID + ") REFERENCES " + MovieDALManager.TABLE_NAME + "(" + MovieDALManager.ID + ")"
		        + ");");
	}
	
	private void createMovieImageTable( SQLiteDatabase db ) {
		db.execSQL("create table " + MovieImageDALManager.TABLE_NAME + " ("
				+ MovieImageDALManager.ROW_ID			 	+ 	" INTEGER PRIMARY KEY, "
				+ MovieImageDALManager.IMAGE_ID		 	+ 	" TEXT, "
		        + MovieImageDALManager.HEIGHT  			+	" INTEGER,"
		        + MovieImageDALManager.WIDTH  			+	" INTEGER,"
		        + MovieImageDALManager.MOVIE_ID  			+	" INTEGER,"
		        + MovieImageDALManager.SIZE  				+	" TEXT,"
		        + MovieImageDALManager.URL  				+	" TEXT,"
		        + MovieImageDALManager.TYPE  				+	" TEXT,"
		        + MovieImageDALManager.PATH  				+	" TEXT, "
		        + "FOREIGN KEY(" + MovieImageDALManager.MOVIE_ID + ") REFERENCES " + MovieDALManager.TABLE_NAME + "(" + MovieDALManager.ID + ")"
		        + ");");
	}
}
